﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Windows.Forms;


namespace CashierSystem.CustomerManager
{
    class CustomerManagerAction : DbConnection
    {
        public static void save(string customerName, string sex, string tel, string cardId, string customerType,string workPlace)
        {
            string date = DateTime.Now.ToString();
            DateTime d = DateTime.Parse(date);
            string sd = d.ToString("yyyy-MM-dd");
            string sql = "insert into t_customer(customerName,sex,tel,cardId,customerType,workPlace,loginTime) ";
            sql += " values('" + customerName + "','" + sex + "','" + tel + "','" + cardId + "','" + customerType + "','" + workPlace + "','" + sd + "')";
            try
            {
                conn.Close();
                conn.Open();  // 数据库连接，父类里的静态变量，已经被初始化
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();//插入数据库
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }

        }
        public static string getMaxCustomerId()
        {
            string sql = "SELECT MAX(customerId) AS Expr1 FROM t_customer";
            string maxCustomerId = "";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Close();
                conn.Open();
                OleDbDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    maxCustomerId = reader["Expr1"].ToString();
                }
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
            return maxCustomerId;

        }

        public static void regSave(string maxCustomerId,string regPrice,string userId)
        {
            string date = DateTime.Now.ToString();
            DateTime d = DateTime.Parse(date);
            string sd = d.ToString("yyyy-MM-dd");
            string sql = "insert into t_regist(customerId,regDate,regPrice,cashier) values('" + maxCustomerId + "','" + sd + "','" + regPrice + "','" + userId + "')";
            try
            {
                conn.Close();
                conn.Open();  // 数据库连接，父类里的静态变量，已经被初始化
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();//插入数据库
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
        }

        public static void loadCustomers(ListView lv,string customerName,string customerType,string workPlace,string startDate,string endDate)
        {
            string sql = "select customerId ,customerName,sex,cardId,tel,customerType,workPlace,loginTime from t_customer where 1=1";
            if (customerName != "")
            {
                sql += " and customerName like '%" + customerName + "%'";

            }
            if (customerType != "")
            {
                sql += " and customerType = '" + customerType + "'";
            }
            if (workPlace != "")
            {
                sql += " and workPlace like '%" + workPlace + "%'";
            }
            if (!startDate.Equals(""))
            {
                sql += " and loginTime >= '" + startDate + "'";
            }
            if (!endDate.Equals(""))
            {
                sql += " and loginTime <= '" + endDate + "'";
            }
            ColumnHeader[] ch = new ColumnHeader[8];
            ch[0] = new ColumnHeader("会员号码", 0, HorizontalAlignment.Left);
            ch[1] = new ColumnHeader("会员姓名", 60, HorizontalAlignment.Left);
            ch[2] = new ColumnHeader("性别", 100, HorizontalAlignment.Left);
            ch[3] = new ColumnHeader("卡号", 120, HorizontalAlignment.Left);
            ch[4] = new ColumnHeader("电话", 100, HorizontalAlignment.Left);
            ch[5] = new ColumnHeader("会员类型", 100, HorizontalAlignment.Left);
            ch[6] = new ColumnHeader("团体标识", 90, HorizontalAlignment.Left);
            ch[7] = new ColumnHeader("录入日期", 90, HorizontalAlignment.Left);
            Utils.dbReaderFillListView(lv, ch, sql, conn);
 
        }

        public static void saveRegIn(string price,string customerId,string cashier)
        {
            string date = DateTime.Now.ToString();
            DateTime d = DateTime.Parse(date);
            string sd = d.ToString("yyyy-MM-dd");
            string sql = "insert into t_regist(customerId,regDate,regPrice,cashier) values('" + customerId + "','" + sd + "','" + price + "','" + cashier + "')";
            try
            {
                conn.Close();
                conn.Open();  // 数据库连接，父类里的静态变量，已经被初始化
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();//插入数据库
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
 
        }
    }
}
